package com.wjx.dao;

import com.wjx.po.Parkingspaces;
import com.wjx.util.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * 数据访问接口实现类，基本JDBC实现，训练JDBC的操作步骤
 *
 * @author yhy *
 */

public class ParkingspacesDaoImpl implements IParkingspacesDao {
    @Override
    public int add(Parkingspaces parkingspaces) {
        int count = 0;
        PreparedStatement st = null;
        //连接数据库
        Connection conn = DBConnection.getConnection();
        //sql语句
        String sql = "insert into parkingspaces values (?,?,?,?,?,?,?,?)";
        try {
            //获取执行sql对象
            st = conn.prepareStatement(sql);
            st.setInt(1, parkingspaces.getPsid());
            st.setString(2, parkingspaces.getOwner());
            st.setString(3, parkingspaces.getPhone());
            st.setString(4, parkingspaces.getStreet());
            st.setInt(5, parkingspaces.getParkingnum());
            st.setString(6, parkingspaces.getStarttime());
            st.setString(7, parkingspaces.getEndtime());
            st.setDouble(8, parkingspaces.getPrice());
            count = st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(conn, st);
        }
        return count;
    }

    @Override
    public int delete(int psid) {
        int count = 0;
        PreparedStatement st = null;
        Connection conn = DBConnection.getConnection();
        String sql = "delete from parkingspaces where psid=?";
        try {
            st = conn.prepareStatement(sql);
            st.setInt(1, psid);
            count = st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(conn, st);
        }
        return count;
    }

    @Override
    public int update(Parkingspaces parkingspaces) {
        PreparedStatement st = null;
        Connection conn = DBConnection.getConnection();
        int count = 0;
        String sql = "update parkingspaces set owner=?,phone=?,street=?,parkingnum=?,starttime=?,endtime=?,price=? where psid=?";
        try {
            st = conn.prepareStatement(sql);
            st.setString(1, parkingspaces.getOwner());
            st.setString(2, parkingspaces.getPhone());
            st.setString(3, parkingspaces.getStreet());
            st.setInt(4, parkingspaces.getParkingnum());
            st.setString(5, parkingspaces.getStarttime());
            st.setString(6, parkingspaces.getEndtime());
            st.setDouble(7, parkingspaces.getPrice());
            st.setInt(8, parkingspaces.getPsid());
            count = st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(conn, st);
        }
        return count;
    }

    @Override
    public List<Parkingspaces> findAll() {
        PreparedStatement st = null;
        Connection conn = DBConnection.getConnection();
        ArrayList<Parkingspaces> list = null;
        ResultSet ex = null;
        String sql = "select * from parkingspaces";
        try {
            st = conn.prepareStatement(sql);
            list = new ArrayList<>();
            ex = st.executeQuery();
            while (ex.next()) {
                Parkingspaces pa = new Parkingspaces();
                pa.setPsid(ex.getInt("psid"));
                pa.setEndtime(ex.getString("endtime"));
                pa.setOwner(ex.getString("owner"));
                pa.setParkingnum(ex.getInt("parkingnum"));
                pa.setPhone(ex.getString("phone"));
                pa.setStarttime(ex.getString("starttime"));
                pa.setStreet(ex.getString("street"));
                pa.setPrice(ex.getDouble("price"));

                list.add(pa);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBConnection.close(conn, st, ex);
        }
        return list;
    }

    @Override
    public Parkingspaces findById(int psid) {
        PreparedStatement ps = null;
        ResultSet st = null;
        Connection conn = DBConnection.getConnection();
        Parkingspaces pa = new Parkingspaces();
        String sql = "select* from parkingspaces where psid=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, psid);
            st = ps.executeQuery();
            while (st.next()) {
                pa.setPsid(st.getInt("psid"));
                pa.setOwner(st.getString("owner"));
                pa.setStreet(st.getString("street"));
                pa.setParkingnum(st.getInt("parkingnum"));
                pa.setStarttime(st.getString("starttime"));
                pa.setEndtime(st.getString("endtime"));
                pa.setPhone(st.getString("phone"));
                pa.setPrice(st.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(conn, ps, st);
        }
        return pa;
    }
}
